package com.geotmt.billingcenter.modules.cost.mapper.provider;

import com.geotmt.billingcenter.common.entry.CostBaseEntry;
import com.geotmt.billingcenter.common.vo.cost.CostVo;
import com.geotmt.billingcenter.common.vo.params.ParamsCost;
import com.geotmt.billingcenter.common.vo.params.ParamsForDel;
import org.apache.ibatis.annotations.Param;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * @Description 成本Base SQL
 * @Author      yanghanwei
 * @Mail        yanghanwei@geotmt.com
 * @Date        2019/3/19 19:02
 * @Version     v1
 **/
public class CostBaseProvider extends WhereProvider{

    private static final Logger logger = LoggerFactory.getLogger(CostBaseProvider.class);

    private static final int NORMAL_STATUS= 1;

    private static final int DELETE_STATUS= 2;

    /**
     * 成本 add base
     * @param costBase
     * @return
     */
    public String add(CostBaseEntry costBase){
        StringBuffer sql = new StringBuffer();
        sql.append("INSERT INTO fin_cost_curr_conf_base (ds_id,out_interface_id,fee_flag,effect_time,")
                .append(" create_user_id,create_time,`status`,`desc`,label,r_id)")
                .append("VALUES")
                .append("(#{dsId},#{outInterfaceId},#{feeFlag},#{effectTime},")
                .append(" #{createUserId},#{createTime}," + NORMAL_STATUS + ",#{desc},#{label},#{rId})");
        logger.info("成本 add base SQL:{}",sql);
        return sql.toString();
    }

    /**
     * 成本 update base
     * @param consumeVo
     * @return
     */
    public String update(CostVo consumeVo){
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE fin_cost_curr_conf_base SET r_id=#{rId},effect_time=#{effectTime},create_user_id=#{createUserId},create_time=#{createTime},update_user_id=#{updateUserId},update_time=#{updateTime},fee_flag=#{feeFlag} where id=#{id}");
        logger.info("成本 update base SQL:{}",sql);
        return sql.toString();
    }

    /**
     * 成本 delete base
     * @param obj
     * @return
     */
    public String delete(ParamsForDel obj){
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE fin_cost_curr_conf_base SET update_user_id=#{updateUserId},update_time=#{updateTime},status=" + DELETE_STATUS + " where id=#{id}");
        logger.info("成本 delete base SQL:{}",sql);
        return sql.toString();
    }

    /**
     * 消耗 list
     * @param obj
     * @return
     */
    public String list(ParamsCost obj){
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT curr.id,curr.out_interface_id outInterfaceId,curr.fee_flag feeFlag,")
                .append(" curr.effect_time effectTime,curr.create_user_id createUserId,curr.create_time createTime,curr.update_user_id updateUserId,")
                .append(" curr.update_time updateTime,curr.`status`,curr.`desc`,")
                .append(" rule.price,rule.`id` rId,rule.`stair_cycle` stairCycle, rule.`stair_start_time` stairStartTime,")
                .append(" ds.name dsName,ds.ds_full_name dsFullName,")
                .append(" inter.interface_name outInterfaceName,")
                .append(" cuserCre.real_name,")
                .append(" cuserUp.real_name")
                .append(" FROM `fin_cost_curr_conf_base` curr")
                .append(" LEFT JOIN `fin_cost_curr_conf_rule` rule ON curr.r_id=rule.id")
                .append(" LEFT JOIN `t_ds_info` ds ON curr.ds_id=ds.id")
                .append(" LEFT JOIN `t_out_interface` inter ON curr.`out_interface_id`=inter.`id`")
                .append(" LEFT JOIN `sys_user` cuserCre ON curr.`create_user_id`= cuserCre.`id`")
                .append(" LEFT JOIN `sys_user` cuserUp ON curr.`update_user_id`=cuserUp.`id`")
                .append(" WHERE 1=1");
        whereCost(sql,"curr", obj);
        logger.info("成本 list SQL:{}",sql);
        return sql.toString();
    }

    /**
     * 添加或修改的回显
     * @return
     */
    public String listAll(ParamsCost obj){
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT #{dsId} dsId,inter.id outInterfaceId,inter.interface_name outInterfaceName, curr.id,curr.fee_flag feeFlag,")
                .append(" curr.effect_time effectTime,curr.create_user_id createUserId,curr.create_time createTime,curr.update_user_id updateUserId,")
                .append(" curr.update_time updateTime,curr.`status`,curr.`desc`,")
                .append(" curr.create_user_id createUserId, curr.create_time createTime,")
                .append(" rule.price,rule.`id` rId,rule.`stair_cycle` stairCycle, rule.`stair_start_time` stairStartTime,")
                .append(" ds.name dsName,ds.ds_full_name dsFullName")
                .append(" FROM ")
                .append("(SELECT out_ter.id, out_ter.interface_name")
                .append(" FROM t_out_interface out_ter")
                .append(" WHERE out_ter.id IN")
                .append(" (SELECT DISTINCT out_iter.id")
                .append(" FROM t_out_interface out_iter")
                .append(" LEFT JOIN t_io_ds iods ON out_iter.id = iods.`out_if_id`")
                .append(" LEFT JOIN t_ds_info ds ON iods.`ds_id` = ds.`id`")
                .append(" WHERE 1=1");
        whereCostListForInter(sql,obj);
        sql.append(")) inter")
                .append(" LEFT JOIN ")
                .append("(SELECT cur.id,cur.fee_flag,cur.effect_time,cur.create_user_id,cur.create_time,cur.update_user_id,")
                .append(" cur.update_time,cur.`status`,cur.`desc`,cur.`out_interface_id`, cur.r_id, cur.ds_id")
                .append(" from fin_cost_curr_conf_base cur")
                .append(" WHERE 1=1");
        whereConsumeListAllForCurr(sql,"cur",obj);
        sql.append(")");
        sql.append(" curr ON curr.`out_interface_id`=inter.`id`")
                .append(" LEFT JOIN `fin_cost_curr_conf_rule` rule ON curr.r_id=rule.id")
                .append(" LEFT JOIN `t_ds_info` ds ON curr.ds_id=ds.id")
                .append(" WHERE 1=1");
        whereCostListAll(sql,"inter", obj);
        logger.info("成本 list SQL:{}",sql);
        return sql.toString();
    }

    /**
     * 成本 getById
     * @param id
     * @return
     */
    public String getById(Integer id){
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT curr.id,curr.out_interface_id outInterfaceId,curr.fee_flag feeFlag,")
                .append(" curr.effect_time effectTime,curr.create_user_id createUserId,curr.create_time createTime,curr.update_user_id updateUserId,")
                .append(" curr.update_time updateTime,curr.`status`,curr.`desc`,")
                .append(" rule.price,rule.`id` rId,rule.`stair_cycle` stairCycle, rule.`stair_start_time` stairStartTime,")
                .append(" ds.name dsName,ds.ds_full_name dsFullName,")
                .append(" inter.interface_name interfaceName,")
                .append(" cuserCre.real_name,")
                .append(" cuserUp.real_name")
                .append(" FROM `fin_cost_curr_conf_base` curr")
                .append(" LEFT JOIN `fin_cost_curr_conf_rule` rule ON curr.r_id=rule.id")
                .append(" LEFT JOIN `t_ds_info` ds ON curr.ds_id=ds.id")
                .append(" LEFT JOIN `t_out_interface` inter ON curr.`out_interface_id`=inter.`id`")
                .append(" LEFT JOIN `sys_user` cuserCre ON curr.`create_user_id`= cuserCre.`id`")
                .append(" LEFT JOIN `sys_user` cuserUp ON curr.`update_user_id`=cuserUp.`id`")
                .append(" WHERE curr.id=#{id}");
        logger.info("成本 getById SQL:{}",sql);
        return sql.toString();
    }

    /**
     * 获取有效规则
     * @param dsId
     * @param outInterfaceId
     * @param dateDay
     * @return
     */
    public String getCost(@Param(value = "dsId") int dsId,
                          @Param(value = "outInterfaceId") int outInterfaceId,
                          @Param(value = "dateDay") String dateDay){
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT t1.id,t1.r_id rId, t1.ds_id dsId,t1.out_interface_id outInterfaceId,t1.fee_flag feeFlag, t1.effect_start_time effectStartTime ,t1.effect_end_time effectEndTime,t1.status,t1.label,")
                .append(" t1.price,t1.stair_cycle stairCycle,t1.stair_start_time stairStartTime,'cost' `type`,t1.sign `sign`")
                .append(" FROM (")
                .append(" SELECT * FROM (")
                .append(" SELECT base.id,base.r_id,base.ds_id,base.out_interface_id,base.fee_flag,DATE_FORMAT(base.effect_time,'%Y-%m-%d') effect_start_time, CURDATE() effect_end_time,base.`status`,base.label,")
                .append(" rule.`price`,rule.`stair_cycle`,rule.`stair_start_time`,'curr' sign")
                .append(" FROM fin_cost_curr_conf_base base")
                .append(" LEFT JOIN `fin_cost_curr_conf_rule` rule ON rule.id = base.`r_id`) t")
                .append(" WHERE t.status =1 AND t.`ds_id`=#{dsId} AND t.out_interface_id=#{outInterfaceId} AND t.effect_start_time <= CURDATE()")
                .append(" UNION ALL ")
                .append(" SELECT baseHis.id,baseHis.r_id, baseHis.ds_id,baseHis.out_interface_id,baseHis.fee_flag,DATE_FORMAT(baseHis.effect_start_time,'%Y-%m-%d'), DATE_FORMAT(baseHis.effect_end_time,'%Y-%m-%d'),baseHis.`status`,baseHis.label,")
                .append(" ruleHis.`price`,ruleHis.`stair_cycle`,ruleHis.`stair_start_time`,'his' sign")
                .append(" FROM fin_cost_his_conf_base baseHis")
                .append(" LEFT JOIN `fin_cost_his_conf_rule` ruleHis ON ruleHis.id = baseHis.`r_id`")
                .append(" WHERE baseHis.status =1 AND baseHis.ds_id=#{dsId} AND baseHis.out_interface_id=#{outInterfaceId}")
                .append(" ) t1")
                .append(" WHERE #{dateDay}>=t1.effect_start_time AND #{dateDay}<=t1.effect_end_time")
                .append(" ORDER BY t1.effect_start_time");
        logger.info("成本 getCost SQL:{}",sql);
        return sql.toString();
    }

}
